using mapshiftApplication.Helpers;
using Microsoft.AspNetCore.Cors;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
using Oracle.ManagedDataAccess.Client;

namespace mapshiftApplication.Controllers;

[ApiController]
[EnableCors("AllowFrontend")]
[Route("api/stdfbinname")]
public class StdfBinNameController : ControllerBase
{
    
         private readonly IOracleConnectionFactory _oracleConnectionFactory;

        public StdfBinNameController(IOracleConnectionFactory oracleConnectionFactory)
        {
            _oracleConnectionFactory = oracleConnectionFactory;
        }
        
    

        [HttpPost]
        [Route("add")]
        public async Task<IActionResult> AddRecord([FromBody] MmsRecord2 record)
        {
            using var oracleConnection = _oracleConnectionFactory.GetConnection("DefaultConnection");
            await oracleConnection.OpenAsync();
            
            using (var command = new OracleCommand(
                "INSERT INTO MMS_STDF_BINNAME  (MMS_PROGRAM_NAME,MMS_BIN_NAME ) VALUES (:programName, :binName)",
                oracleConnection))
            {
                command.Parameters.Add(new OracleParameter("MMS_PROGRAM_NAME", record.Mms_program_name));
                command.Parameters.Add(new OracleParameter("MMS_BIN_NAME", record.Mms_bin_name));

                int rows = await command.ExecuteNonQueryAsync();
                return rows > 0 ? Ok(new { message = "Record added successfully",code = "200" }) : BadRequest("Insert failed");
            }
        }
        
        
        
        [HttpGet]
        [Route("query")]
        public async Task<IActionResult> GetRecords([FromQuery] int page = 1, [FromQuery] int pageSize = 10, [FromQuery] string programName = "")
        {
            using var oracleConnection = _oracleConnectionFactory.GetConnection("DefaultConnection");
            await oracleConnection.OpenAsync();
            string sql = "SELECT * FROM MMS_STDF_BINNAME ";
            
            string countSql = "SELECT COUNT(*) FROM MMS_STDF_BINNAME WHERE 1=1";
            if (!string.IsNullOrEmpty(programName)) countSql += " AND MMS_PROGRAM_NAME = :programName";
            using var countCommand = new OracleCommand(countSql, oracleConnection);
            if (!string.IsNullOrEmpty(programName)) countCommand.Parameters.Add(new OracleParameter("MMS_PROGRAM_NAME", programName));

            int totalRecords = Convert.ToInt32(await countCommand.ExecuteScalarAsync());
            
            List<OracleParameter> parameters = new List<OracleParameter>();
            List<string> conditions = new List<string>();
            if (!string.IsNullOrEmpty(programName))
            {
                conditions.Add("MMS_PROGRAM_NAME = :programName");
                parameters.Add(new OracleParameter("MMS_PROGRAM_NAME", programName));
            }
            
            
            if (conditions.Count > 0)
            {
                sql += " WHERE " + string.Join(" AND ", conditions);
            }
            sql += " OFFSET :offset ROWS FETCH NEXT :pageSize ROWS ONLY";
            parameters.Add(new OracleParameter("offset", (page - 1) * pageSize));
            parameters.Add(new OracleParameter("pageSize", pageSize));

            using (var command = new OracleCommand(sql, oracleConnection))
            {
               command.Parameters.AddRange(parameters.ToArray());

                using (var reader = await command.ExecuteReaderAsync())
                {
                    var records = new List<MmsRecord2>();
                    while (await reader.ReadAsync())
                    {
                        records.Add(new MmsRecord2
                        {
                            Mms_program_name = reader["MMS_PROGRAM_NAME"].ToString()!,
                            Mms_bin_name = reader["MMS_BIN_NAME"].ToString()!
                        });
                    }
                    return Ok(new { items = records, total = totalRecords });
                }
            }
        }
        
        
        [HttpGet]
        [Route("delete/{id}")]
        public async Task<IActionResult> DeleteRecord(string id)
        {
            using var oracleConnection = _oracleConnectionFactory.GetConnection("DefaultConnection");
            await oracleConnection.OpenAsync();
    
            string sql = "DELETE FROM MMS_STDF_BINNAME WHERE MMS_PROGRAM_NAME = :programName";
    
            using (var command = new OracleCommand(sql, oracleConnection))
            {
                command.Parameters.Add(new OracleParameter("id", id));

                int rowsAffected = await command.ExecuteNonQueryAsync();
        
                return rowsAffected > 0 ? Ok("Record deleted successfully.") : NotFound("Record not found.");
            }
        }
        
        [HttpPost]
        [Route("edit")]
        public async Task<IActionResult> EditRecord( [FromBody] MmsRecord2 record)
        {
            using var oracleConnection = _oracleConnectionFactory.GetConnection("DefaultConnection");
            await oracleConnection.OpenAsync();
            
            string sql = $@"
                UPDATE MMS_STDF_BINNAME 
                SET MMS_BIN_NAME = '{record.Mms_bin_name}'
                WHERE MMS_PROGRAM_NAME = '{record.Mms_program_name}'";

            LogHelper.Info("Final Executing SQL: " + sql);

            
            using (var command = new OracleCommand(sql, oracleConnection))
            {
                int rows = await command.ExecuteNonQueryAsync();
                
                return rows > 0 ? Ok("Record update successfully.") : NotFound("Record not found.");
            }
        }
    
    
    public class MmsRecord2
    {
        
        public required string Mms_program_name { get; set; }
        public string?  Mms_bin_name { get; set; }
    }

    
}